﻿using BackupContracts.Sql;
using BackupManager.Configuration;
using BackupManager.Sql.Data;
using BackupManager.Storage;
using BackupManager.Utils;
using Microsoft.SqlServer.Dac;
using Microsoft.WindowsAzure.ServiceRuntime;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.StorageClient;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace BackupManager.Sql
{
    public class SqlDatabaseProvider
    {
        private const string _PartiotionKey = "servers";
        private CloudStorageAccount _StorageAccount;
        private string _EncryptionKey;

        public SqlDatabaseProvider(CloudStorageAccount storageAccount, string encryptionKey)
        {
            _StorageAccount = storageAccount;
            _EncryptionKey = encryptionKey;
        }

        public void UpsertServer(SqlServer server)
        {
            var sqlServerEntity = new SqlServerEntity()
            {
                Name = server.Name,
                PartitionKey = _PartiotionKey,
                RowKey = server.Id,
                User = server.User
            };

            if (server.Password != null)
                sqlServerEntity.Password = EncryptionHelper.EncryptString(server.Password, _EncryptionKey);

            var entityContext = new SqlServerEntityContext(_StorageAccount.CreateCloudTableClient());
            entityContext.Upsert(sqlServerEntity);
        }

        public void UpsertDatabase(SqlDatabase database)
        {
            var databaseEntity = new SqlDatabaseEntity()
            {
                PartitionKey = database.Server.Name,
                RowKey = database.Name,
                LastBackup = TableStorageHelper.NullableDateTimeOffsetToDateTime(database.LastBackup),
                Status = database.Status,
            };

            var entityContext = new SqlDatabaseEntityContext(_StorageAccount.CreateCloudTableClient());
            entityContext.Upsert(databaseEntity);
        }

        public void UpsertDatabaseBackup(SqlDatabaseBackup databaseBackup)
        {
            var databaseBackupEntity = new SqlDatabaseBackupEntity()
            {
                PartitionKey = string.Format("{0}|{1}", databaseBackup.Database.Server.Name, databaseBackup.Database.Name),
                RowKey = databaseBackup.Id,
                Start = TableStorageHelper.NullableDateTimeOffsetToDateTime(databaseBackup.Start),
                Finish = TableStorageHelper.NullableDateTimeOffsetToDateTime(databaseBackup.Finish),
                Status = databaseBackup.Status,
                BackupUrl = databaseBackup.BackupUrl,
            };

            var entityContext = new SqlDatabaseBackupEntityContext(_StorageAccount.CreateCloudTableClient());
            entityContext.Upsert(databaseBackupEntity);
        }

        public SqlDatabase GetDatabase(string serverName, string databaseName)
        {
            try
            {
                var entityContext = new SqlDatabaseEntityContext(_StorageAccount.CreateCloudTableClient());
                var sqlDatabaseEntity = (from entity in entityContext.CreateQuery()
                                         where entity.PartitionKey.Equals(serverName)
                                         && entity.RowKey.Equals(databaseName)
                                         select entity).FirstOrDefault();
                if (sqlDatabaseEntity != null)
                    return EntityToModel(sqlDatabaseEntity);
            }
            catch { }

            var sqlServer = ListServers().FirstOrDefault((s) => s.Name.Equals(serverName));

            return new SqlDatabase()
            {
                LastBackup = null,
                Name = databaseName,
                Server = sqlServer,
                ServerId = sqlServer != null ? sqlServer.Id : null,
            };
        }


        public void DeleteServer(string serverId)
        {
            var entityContext = new SqlServerEntityContext(_StorageAccount.CreateCloudTableClient());
            entityContext.Delete(_PartiotionKey, serverId);
        }


        public List<SqlDatabaseBackup> ListDatabaseBackups(string serverName, string databaseName)
        {
            var partitionKey = string.Format("{0}|{1}", serverName, databaseName).ToLower();
            var entityContext = new SqlDatabaseBackupEntityContext(_StorageAccount.CreateCloudTableClient());
            return (from entity in entityContext.CreateQuery()
                    where (partitionKey == "|" || entity.PartitionKey == partitionKey)
                    select entity).ToList().Select((e) => EntityToModel(e)).ToList();
        }

        public List<SqlDatabaseBackupLog> ListDatabaseBackupLogs(string serverName, string databaseName, string backupId)
        {
            var partitionKey = string.Format("{0}|{1}|{2}", serverName, databaseName, backupId).ToLower();
            var entityContext = new BackupLogEntityContext(_StorageAccount.CreateCloudTableClient());
            return (from entity in entityContext.CreateQuery()
                    where (partitionKey == "||" || entity.PartitionKey == partitionKey)
                    select entity).ToList().Select((e) => EntityToModel(e)).ToList();
        }

        private SqlDatabaseBackupLog EntityToModel(BackupLogEntity e)
        {
            var keys = e.PartitionKey.Split('|');
            return new SqlDatabaseBackupLog()
            {
                Date = new DateTimeOffset(e.Timestamp),
                Exception = e.Exception,
                Id = e.RowKey,
                Message = e.Message,
                MessageCategory = e.MessageCategory,
            };
        }

        public List<SqlDatabase> ListDatabases(string serverName)
        {
            var partitionKey = string.Format("{0}", serverName).ToLower();
            var entityContext = new SqlDatabaseEntityContext(_StorageAccount.CreateCloudTableClient());
            return (from entity in entityContext.CreateQuery()
                    where (partitionKey == "" || entity.PartitionKey == partitionKey)
                    select entity).ToList().Select((e) => EntityToModel(e)).ToList();
        }

        private SqlDatabase EntityToModel(SqlDatabaseEntity entity)
        {
            return new SqlDatabase()
            {
                Server = new SqlServer()
                {
                    Name = entity.PartitionKey,
                },
                Name = entity.RowKey,
                LastBackup = TableStorageHelper.DateTimeToNullableDateTimeOffset(entity.LastBackup),
                Status = entity.Status,
            };
        }

        private SqlDatabaseBackup EntityToModel(SqlDatabaseBackupEntity entity)
        {
            var keys = entity.PartitionKey.Split('|');
            return new SqlDatabaseBackup()
            {
                Id = entity.RowKey,
                Database = new SqlDatabase()
                {
                    Server = new SqlServer()
                    {
                        Name = keys[0],
                    },
                    Name = keys[1],
                },
                BackupUrl = entity.BackupUrl,
                Finish = TableStorageHelper.DateTimeToNullableDateTimeOffset(entity.Finish),
                Start = TableStorageHelper.DateTimeToNullableDateTimeOffset(entity.Start),
                Status = entity.Status,
            };
        }

        public List<SqlServer> ListServers()
        {
            var entityContext = new SqlServerEntityContext(_StorageAccount.CreateCloudTableClient());
            return (from entity in entityContext.CreateQuery().ToList()
                    select EntityToModel(entity)).ToList();
        }

        private static SqlServer EntityToModel(SqlServerEntity entity)
        {
            return new SqlServer()
            {
                Id = entity.RowKey,
                Name = entity.Name,
                User = entity.User,
            };
        }

        public List<SqlDatabase> FetchDatabases(string serverId)
        {
            var entityContext = new SqlServerEntityContext(_StorageAccount.CreateCloudTableClient());
            var sqlServerEntity = entityContext.GetEntity(_PartiotionKey, serverId);

            if (sqlServerEntity != null)
            {
                var sqlServer = EntityToModel(sqlServerEntity);

                var password = EncryptionHelper.DecryptString(sqlServerEntity.Password, _EncryptionKey);
                var serverName = string.Format("tcp:{0}.database.windows.net,1433", sqlServerEntity.Name);
                var username = string.Format("{0}@{1}", sqlServerEntity.User, sqlServerEntity.Name);
                var connectionString = GetServerConnection(serverName, "master", username, password);

                var result = new List<SqlDatabase>();

                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    var queryCommand = connection.CreateCommand();
                    queryCommand.CommandText = "SELECT dbs.name FROM sys.databases as dbs";
                    using (var reader = queryCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var databaseName = reader.GetString(0);
                            result.Add(new SqlDatabase()
                            {
                                Name = databaseName,
                                Server = sqlServer,
                            });
                        }
                    }
                }

                return result;
            }
            return null;
        }

        private static string GetServerConnection(string serverName, string databaseName, string loginId, string password)
        {
            if (!serverName.Contains(".database.windows.net"))
                serverName += ".database.windows.net";

            var sqlConnectionStringBuilder = new SqlConnectionStringBuilder();

            sqlConnectionStringBuilder.DataSource = serverName;
            sqlConnectionStringBuilder.TrustServerCertificate = true;
            sqlConnectionStringBuilder.InitialCatalog = databaseName;
            sqlConnectionStringBuilder.Encrypt = true;
            sqlConnectionStringBuilder.IntegratedSecurity = false;
            sqlConnectionStringBuilder.UserID = loginId;
            sqlConnectionStringBuilder.Password = password;
            //sqlConnectionStringBuilder.ConnectTimeout = 60;
            //sqlConnectionStringBuilder.MinPoolSize = 5;
            //sqlConnectionStringBuilder.MaxPoolSize = 50;        // According to Azure team there is NO max limit on connections.  It all comes down to CPU and IO usage.  They said setting this to 100 would be fine.
            //sqlConnectionStringBuilder.Pooling = true;
            return sqlConnectionStringBuilder.ToString();
        }

        private static bool CopyDB(string serverName, string loginId, string password, string databaseName, string databaseCopy, CloudStorageAccount storageAccount, SqlDatabaseBackup sqlDatabaseBackup)
        {
            try
            {
                InsertBackupLog(sqlDatabaseBackup, "Copy Status", String.Format("Copying '{0}' to '{1}'...", databaseName, databaseCopy), storageAccount);

                var connectionString = GetServerConnection(serverName, "master", loginId, password);

                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    var copyCommand = connection.CreateCommand();
                    copyCommand.CommandText = String.Format("CREATE DATABASE {1} AS COPY OF {0}", databaseName, databaseCopy);
                    copyCommand.ExecuteNonQuery();
                    var queryCommand = connection.CreateCommand();
                    queryCommand.CommandText = "SELECT dbs.state_desc FROM sys.databases as dbs WHERE dbs.name = @databaseName";
                    queryCommand.Parameters.AddWithValue("@databaseName", databaseCopy);
                    var successfullyCopied = false;
                    while (true)
                    {
                        string databaseState = string.Empty;
                        using (var reader = queryCommand.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                databaseState = reader.GetString(0);
                            }
                        }
                        //if (databaseState.Equals("COPYING", StringComparison.InvariantCultureIgnoreCase)
                        if (databaseState.Equals("ONLINE", StringComparison.InvariantCultureIgnoreCase))
                        {
                            InsertBackupLog(sqlDatabaseBackup, "Copy Status", String.Format("Copy '{1}' ready.", databaseName, databaseCopy), storageAccount);
                            successfullyCopied = true;
                            break;
                        }
                        else if (databaseState.Equals("SUSPECT", StringComparison.InvariantCultureIgnoreCase))
                        {
                            InsertBackupLog(sqlDatabaseBackup, "Copy Status", String.Format("Copy '{1}' error.", databaseName, databaseCopy), storageAccount);
                            successfullyCopied = false;
                            break;
                        }
                        System.Threading.Thread.Sleep(1000);
                    }

                    if (!successfullyCopied)
                    {
                        DropDB(connection, databaseCopy);
                    }

                    return successfullyCopied;
                }
            }
            catch (Exception ex)
            {
                InsertBackupLog(sqlDatabaseBackup, "Error", string.Format("Error copying database to '{0}'.", databaseCopy), storageAccount, ex);
            }
            return false;
        }

        private static void DropDB(SqlConnection connection, string databaseCopy)
        {
            if (databaseCopy.Contains("_Bkp_") && databaseCopy.EndsWith("_SqlStrBkp"))
            {
                var dropCopyCommand = connection.CreateCommand();
                dropCopyCommand.CommandText = String.Format("DROP DATABASE {0}", databaseCopy);
                dropCopyCommand.ExecuteNonQuery();
            }
        }

        private static void DropDB(string serverName, string loginId, string password, string databaseCopy, string databaseName, CloudStorageAccount storageAccount, SqlDatabaseBackup sqlDatabaseBackup)
        {
            try
            {
                if (sqlDatabaseBackup != null)
                {
                    InsertBackupLog(sqlDatabaseBackup, "Copy Status", String.Format("Deleting copy '{1}'...", databaseName, databaseCopy), storageAccount);
                }

                var connectionString = GetServerConnection(serverName, "master", loginId, password);
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    DropDB(connection, databaseCopy);

                    if (sqlDatabaseBackup != null)
                    {
                        InsertBackupLog(sqlDatabaseBackup, "Copy Status", String.Format("Copy '{1}' deleted.", databaseName, databaseCopy), storageAccount);
                    }
                }
            }
            catch (Exception ex)
            {
                if (sqlDatabaseBackup != null)
                {
                    InsertBackupLog(sqlDatabaseBackup, "Error", string.Format("Error deleting database copy '{0}'.", databaseCopy), storageAccount, ex);
                }
            }
        }

        private static string BackupToBlob(string serverName, string loginId, string password, string databaseCopyName, string databaseName, CloudStorageAccount storageAccount, string container, int maxHistory, SqlDatabaseBackup sqlDatabaseBackup)
        {
            var connectionString = GetServerConnection(serverName, "master", loginId, password);
            try
            {
                var blobStorage = storageAccount.CreateCloudBlobClient();
                var blobContainer = blobStorage.GetContainerReference(container);
                blobContainer.CreateIfNotExists();
                string backupUrl = null;

                var directory = Path.Combine(RoleEnvironment.GetLocalResource("BackupLocalStorage").RootPath, @"SQLAzureBackups\");
                Directory.CreateDirectory(directory);

                var backupFileName = Path.Combine(directory, databaseName + "_" + DateTime.UtcNow.ToString("yyyy-MM-dd HHmmss") + ".bacpac");

                var blobDirectoryName = Path.Combine(serverName, databaseName);
                var blobFileName = Path.Combine(blobDirectoryName, DateTime.UtcNow.ToString("yyyy-MM-dd"), databaseName + " " + DateTime.UtcNow.ToString("yyyy-MM-dd HHmmss") + ".bacpac");
                blobFileName = blobFileName.Replace("\\", "/");

                var dacStore = new DacServices(connectionString);
                dacStore.Message += (sender, e) =>
                {
                    InsertBackupLog(sqlDatabaseBackup, "DAC Message", Convert.ToString(e.Message), storageAccount);
                };
                dacStore.ProgressChanged += (sender, e) =>
                {
                    InsertBackupLog(sqlDatabaseBackup, "DAC Message", String.Format("{0}: {1}", e.Message, e.Status), storageAccount);

                    try
                    {
                        if (e.Status == DacOperationStatus.Completed && e.Message.Equals("Exporting data from database"))
                        {
                            var blob = blobContainer.GetBlockBlobReference(blobFileName);
                            blob.Metadata["Date"] = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss");

                            using (var awaiter = new ManualResetEvent(false))
                            {
                                var blobTransferHelper = new BlobTransferHelper();
                                blobTransferHelper.TransferCompleted += (sender1, e1) =>
                                {
                                    InsertBackupLog(sqlDatabaseBackup, "Blob Status", String.Format("Backup uploaded to blob '{0}'", blob.Uri), storageAccount);

                                    File.Delete(backupFileName);
                                    awaiter.Set();
                                };
                                blobTransferHelper.UploadBlobAsync(blob, backupFileName);
                                backupUrl = blob.Uri.ToString();
                                awaiter.WaitOne();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        InsertBackupLog(sqlDatabaseBackup, "Error", "Error on backup process.", storageAccount, ex);
                    }
                };
                dacStore.ExportBacpac(backupFileName, databaseCopyName, DacSchemaModelStorageType.File);

                try
                {
                    var blobDirectory = blobContainer.GetDirectoryReference(blobDirectoryName);
                    var blobs = blobDirectory.ListBlobs(true).ToList();
                    blobs.Sort((x, y) => x.Uri.PathAndQuery.CompareTo(y.Uri.PathAndQuery));
                    while (blobs.Count > maxHistory)
                    {
                        var blobItem = blobs[0];
                        var blob = blobContainer.GetBlockBlobReference(blobItem.Uri.ToString());
                        blob.Delete();
                        blobs.Remove(blobItem);
                    }
                }
                catch (Exception ex)
                {
                    InsertBackupLog(sqlDatabaseBackup, "Error", "Error on deleting older backups.", storageAccount, ex);
                }

                return backupUrl;
            }
            catch (Exception ex)
            {
                InsertBackupLog(sqlDatabaseBackup, "Error", "Error on backup process.", storageAccount, ex);
            }
            return null;
        }

        private static void InsertBackupLog(SqlDatabaseBackup sqlDatabaseBackup, string messageCategory, string message, CloudStorageAccount storageAccount, Exception exception = null)
        {
            var backupLogEntity = new BackupLogEntity()
            {
                PartitionKey = string.Format("{0}|{1}|{2}", sqlDatabaseBackup.Database.Server.Name, sqlDatabaseBackup.Database.Name, sqlDatabaseBackup.Id),
                RowKey = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"),
                Message = message,
                MessageCategory = messageCategory,
                Exception = String.Format("{0}", exception),
            };
            var entityContext = new BackupLogEntityContext(storageAccount.CreateCloudTableClient());
            entityContext.Upsert(backupLogEntity);
        }

        public static void CopyAndBackupToBlob(string serverName, string databaseName, string loginId, string password, CloudStorageAccount storageAccount, string container, int maxHistory)
        {
            serverName = serverName.ToLower();
            databaseName = databaseName.ToLower();

            var provider = new SqlDatabaseProvider(CloudStorageAccount.Parse(ConfigManager.Instance.StorageConnectionString), ConfigManager.Instance.EncryptionKey);
            var database = provider.GetDatabase(serverName, databaseName);
            database.Status = "Backup in Progress";
            provider.UpsertDatabase(database);

            var backup = new SqlDatabaseBackup()
            {
                Database = database,
                Start = new DateTimeOffset(DateTime.UtcNow),
                Id = Guid.NewGuid().ToString(),
                Status = "Backup in progress",
            };
            provider.UpsertDatabaseBackup(backup);

            try
            {
                var databaseCopy = string.Format("{0}_Bkp_{1:yyyy_MM_dd__HH_mm_ss}_SqlStrBkp", databaseName, DateTime.UtcNow);
                if (CopyDB(serverName, loginId, password, databaseName, databaseCopy, storageAccount, backup))
                {
                    try
                    {
                        string backupUrl = BackupToBlob(serverName, loginId, password, databaseCopy, databaseName, storageAccount, container, maxHistory, backup);
                        if (!string.IsNullOrEmpty(backupUrl))
                        {
                            try
                            {
                                database = provider.GetDatabase(serverName, databaseName);
                                database.Status = "Backup Ready";
                                database.LastBackup = new DateTimeOffset(DateTime.UtcNow);
                                provider.UpsertDatabase(database);

                                backup.Status = database.Status;
                                backup.BackupUrl = backupUrl;
                                backup.Finish = database.LastBackup;
                                provider.UpsertDatabaseBackup(backup);
                            }
                            catch (Exception ex)
                            {
                                InsertBackupLog(backup, "Exception", "Error updating status.", storageAccount, ex);
                            }
                        }
                    }
                    finally
                    {
                        DropDB(serverName, loginId, password, databaseCopy, databaseName, storageAccount, backup);
                    }
                }
            }
            catch (Exception ex)
            {
                InsertBackupLog(backup, "Error", "Error on the backup process.", storageAccount, ex);

                database = provider.GetDatabase(serverName, databaseName);
                database.Status = "Backup Error";
                provider.UpsertDatabase(database);

                backup.Status = database.Status;
                backup.Finish = new DateTimeOffset(DateTime.UtcNow);
                provider.UpsertDatabaseBackup(backup);
            }
        }

        public SqlServer GetServer(string serverId)
        {
            var entityContext = new SqlServerEntityContext(_StorageAccount.CreateCloudTableClient());
            var sqlServerEntity = entityContext.GetEntity(_PartiotionKey, serverId);
            if (sqlServerEntity != null)
            {
                var sqlServer = EntityToModel(sqlServerEntity);
                sqlServer.Password = EncryptionHelper.DecryptString(sqlServerEntity.Password, _EncryptionKey);
                return sqlServer;
            }
            return null;
        }

        public void CloseUnfinishedSqlBackups()
        {
            var currentDateTime = new DateTimeOffset(DateTime.UtcNow);
            var timeout = TimeSpan.FromHours(1);

            var allDatabases = ListDatabases(null);

            foreach (var database in allDatabases)
            {
                try
                {
                    var allBackups = ListDatabaseBackups(database.Server.Name, database.Name);
                    foreach (var backup in allBackups)
                    {
                        try
                        {
                            if (backup.Start.HasValue)
                            {
                                if (!backup.Finish.HasValue && backup.Start.Value.Add(timeout) < currentDateTime)
                                {
                                    InsertBackupLog(backup, "Error", "Error on the backup process (Timeout).", _StorageAccount);

                                    backup.Status = "Backup Error";
                                    backup.Finish = new DateTimeOffset(DateTime.UtcNow);
                                    UpsertDatabaseBackup(backup);
                                }
                            }
                        }
                        catch { }

                    }

                    var lastBackup = ListDatabaseBackups(database.Server.Name, database.Name).OrderBy((b) => b.Start).LastOrDefault();
                    if (lastBackup != null)
                    {
                        var database1 = GetDatabase(database.Server.Name, database.Name);
                        database1.Status = lastBackup.Status;
                        UpsertDatabase(database1);
                    }
                }
                catch { }
            }
        }

        public void DeleteUnfinishedSqlBackupCopies()
        {
            var currentDateTime = new DateTimeOffset(DateTime.UtcNow);
            var timeout = TimeSpan.FromHours(1);
            var allServers = ListServers();
            var template = "yyyy_MM_dd__HH_mm_ss";
            var sufix = "_SqlStrBkp";

            foreach (var server in allServers)
            {
                try
                {
                    var sqlDatabaseProvider = new SqlDatabaseProvider(_StorageAccount, ConfigManager.Instance.EncryptionKey);
                    var server1 = sqlDatabaseProvider.GetServer(server.Id);

                    var databaseCopies = FetchDatabases(server.Id).Where((d) => d.Name.EndsWith("_SqlStrBkp"));
                    foreach (var databaseCopy in databaseCopies)
                    {
                        try
                        {
                            var datePart = databaseCopy.Name.Substring(databaseCopy.Name.Length - template.Length - sufix.Length, template.Length);
                            var backupTime = new DateTimeOffset(DateTime.ParseExact(datePart, template, CultureInfo.InvariantCulture), TimeSpan.FromHours(0));
                            if (backupTime.Add(timeout) < currentDateTime)
                            {
                                DropDB(server1.Name, server1.User, server1.Password, databaseCopy.Name, null, null, null);
                            }
                        }
                        catch { }

                    }
                }
                catch { }

            }
        }
    }
}
